Understanding SQL Commands and Syntax – Complete MySQL Notes

SQL (Structured Query Language) is the standard language used to communicate with relational database management systems such as MySQL. SQL commands allow developers and database administrators to create databases, define tables, insert records, retrieve information, update existing data, delete records, and manage database access. Understanding SQL commands and syntax is essential for database development, backend programming, reporting, and data analytics.

Explore the JustAcademy MySQL Training Course and Register for a MySQL Course Demo to strengthen your practical SQL skills.

1. Learning Objectives

After completing these notes, learners will be able to:

• Understand SQL and its importance in database management.
• Learn the basic structure and syntax of SQL statements.
• Identify major categories of SQL commands.
• Create databases and tables using SQL.
• Insert, retrieve, update, and delete records.
• Filter, sort, and group data using SQL clauses.
• Understand SQL operators, constraints, and aggregate functions.
• Use aliases, comments, and formatting correctly.
• Recognize common SQL syntax errors and correct them.
• Build and verify a small MySQL database through practical exercises.

2. What Is SQL?

SQL stands for Structured Query Language. It is used to define, manipulate, retrieve, and control data in relational databases.

MySQL understands SQL statements and executes them against the selected database, provided the connected account has the required privileges.

Example

SELECT * FROM students;

This statement retrieves all columns and rows from a table named students.

Why Is SQL Important?

• It helps organize information into relational tables.
• It retrieves specific information from large datasets.
• It allows authorized users to add, modify, and remove records.
• It supports business reporting and analytical queries.
• It enables applications to interact with databases.
• It provides commands for permissions, transactions, and database administration.

Applications of SQL

3. Understanding SQL Syntax

SQL syntax refers to the rules that determine how SQL statements must be written so that the database can understand and execute them.

Basic SQL Statement Structure

SELECT column_name
FROM table_name
WHERE condition;

Explanation

• SELECT specifies the columns to retrieve.
• FROM identifies the table containing the data.
• WHERE filters rows according to a condition.
• ; marks the end of the statement in typical MySQL interactive usage.

Example

SELECT student_name
FROM students
WHERE city = 'Mumbai';

This query retrieves the names of students whose city is Mumbai.

Important Syntax Rules

• Write SQL keywords in a consistent style. Uppercase keywords are commonly used for readability, but MySQL generally allows keywords in lowercase as well.
• Separate column names and expressions with commas.
• Use single quotes for ordinary text values.
• Use parentheses where required for function arguments or to group expressions.
• Place clauses in the correct order.
• Use semicolons to terminate statements in interactive clients and SQL scripts where appropriate.
• Check spelling of table names, column names, and database names.
• Use the correct data type and comparison operator for each condition.

4. SQL Statement Example and Execution Flow

Consider the following SQL statement:

SELECT student_name, course
FROM students
WHERE city = 'Pune'
ORDER BY student_name ASC;

The query asks MySQL to retrieve student names and courses for students in Pune, then sort the matching rows by student name in ascending order.

Execution Flow

1. The client sends the SQL statement to MySQL Server.
2. MySQL parses the statement and checks its syntax.
3. The server checks whether the referenced objects exist and whether the account has the required privileges.
4. The query optimizer determines an execution strategy.
5. The server executes the query and returns the result set.

This is a simplified explanation. Actual query processing depends on the statement, server configuration, and database structure.

5. Categories of SQL Commands

SQL commands are commonly grouped into categories according to their purpose. These categories are useful for learning, although some commands may have additional administrative or transactional effects.

6. DDL Commands – Data Definition Language

DDL commands are used to create, modify, and remove database structures such as databases, tables, and indexes.

6.1 CREATE DATABASE

The CREATE DATABASE command creates a database.

CREATE DATABASE college_db;

To avoid an error if the database already exists, use:

CREATE DATABASE IF NOT EXISTS college_db;

6.2 USE

The USE statement selects the database for the current session.

USE college_db;

6.3 CREATE TABLE

The CREATE TABLE command creates a table with specified columns and constraints.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course VARCHAR(100),
    city VARCHAR(100)
);

6.4 ALTER TABLE

The ALTER TABLE command changes an existing table's structure.

Add a new column:

ALTER TABLE students
ADD email VARCHAR(150);

Modify a column's definition:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(150) NOT NULL;

Drop a column:

ALTER TABLE students
DROP COLUMN email;

Warning: Dropping a column removes that column and its stored values. Review the table structure and take a backup when necessary.

6.5 TRUNCATE TABLE

The TRUNCATE TABLE command removes all rows from a table while keeping the table structure.

TRUNCATE TABLE students;

In MySQL, TRUNCATE TABLE is a DDL statement with implicit-commit behavior. Do not assume you can undo it with a normal transaction rollback.

6.6 DROP TABLE

The DROP TABLE command removes a table definition and its data.

DROP TABLE students;

6.7 DROP DATABASE

The DROP DATABASE command removes a database and its contained objects.

DROP DATABASE college_db;

Important: DROP and TRUNCATE can permanently remove data. Use them only when the target and intended outcome have been verified.

7. DML Commands – Data Manipulation Language

DML commands are used to add, update, and delete records in tables.

7.1 INSERT

The INSERT command adds new rows to a table.

INSERT INTO students (student_name, course, city)
VALUES ('Rahul Sharma', 'MySQL', 'Mumbai');

Insert multiple records:

INSERT INTO students (student_name, course, city)
VALUES
('Priya Patel', 'Python', 'Pune'),
('Amit Verma', 'Data Analytics', 'Delhi'),
('Neha Singh', 'MySQL', 'Mumbai');

7.2 UPDATE

The UPDATE command changes existing records.

UPDATE students
SET city = 'Navi Mumbai'
WHERE student_id = 1;

The WHERE clause identifies the row or rows to update. Without a WHERE clause, the command can update every row in the table.

7.3 DELETE

The DELETE command removes selected rows from a table.

DELETE FROM students
WHERE student_id = 3;

Without a WHERE clause, all rows in the table may be deleted while the table itself remains.

8. DQL Command – SELECT

SELECT is used to retrieve information from one or more tables. It is one of the most frequently used SQL statements.

8.1 Select All Columns

SELECT * FROM students;

The asterisk (*) selects all visible columns from the referenced table.

8.2 Select Specific Columns

SELECT student_name, course
FROM students;

8.3 Use WHERE to Filter Rows

SELECT *
FROM students
WHERE city = 'Mumbai';

8.4 Sort Results with ORDER BY

SELECT student_name, city
FROM students
ORDER BY student_name ASC;

ASC sorts in ascending order, while DESC sorts in descending order.

8.5 Limit the Number of Results

SELECT *
FROM students
LIMIT 5;

This returns at most five rows. Without an ORDER BY clause, the returned rows are not guaranteed to be in a particular order.

9. SQL Clauses and Their Uses

Clauses are parts of SQL statements that perform specific functions, such as identifying tables, filtering rows, grouping records, or sorting results.

Example Using Multiple Clauses

SELECT city, COUNT(*) AS total_students
FROM students
WHERE course = 'MySQL'
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY total_students DESC
LIMIT 10;

This query filters records to the MySQL course, groups them by city, retains groups with more than one matching student, sorts the groups by their counts, and returns at most ten rows.

Typical clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

10. SQL Operators

Operators are symbols or keywords used to compare values, combine conditions, and perform calculations.

10.1 Comparison Operators

10.2 Logical Operators

SELECT *
FROM students
WHERE city = 'Mumbai' AND course = 'MySQL';

AND requires both conditions to be true. OR accepts rows where at least one condition is true, while NOT negates a condition.

10.3 BETWEEN

SELECT *
FROM exam_results
WHERE marks BETWEEN 60 AND 80;

BETWEEN includes both boundary values for ordinary numeric comparisons.

10.4 IN

SELECT *
FROM students
WHERE city IN ('Mumbai', 'Pune', 'Delhi');

IN checks whether a value matches any value in a specified list.

10.5 LIKE

SELECT *
FROM students
WHERE student_name LIKE 'A%';

In a LIKE pattern, % represents zero or more characters and _ represents one character.

10.6 IS NULL

SELECT *
FROM students
WHERE course IS NULL;

Use IS NULL or IS NOT NULL to test for missing values. Do not use = NULL for this purpose.

11. Aggregate Functions

Aggregate functions calculate a summary value from a set of rows.

Example: Count Students by City

SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city;

This query groups students by city and calculates the number of rows in each group.

12. SQL Constraints

Constraints enforce rules on table data to help maintain accuracy and consistency.

Example Table with Constraints

CREATE TABLE exam_results (
    result_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    marks INT CHECK (marks BETWEEN 0 AND 100),
    status VARCHAR(20) DEFAULT 'Pending'
);

Supported constraint behavior can depend on the MySQL version and storage engine. Use the documentation for your installed version when implementing production rules.

13. SQL Comments and Formatting

Comments explain SQL code and help developers understand the purpose of statements. MySQL supports common comment styles.

13.1 Single-Line Comment Using --

-- Display all students
SELECT * FROM students;

In MySQL, the -- comment marker must be followed by whitespace or a control character.

13.2 Single-Line Comment Using #

# Count all students
SELECT COUNT(*) FROM students;

13.3 Multi-Line Comment

/*
This query retrieves student names
and their courses.
*/
SELECT student_name, course
FROM students;

Formatting Best Practices

• Put major SQL clauses on separate lines.
• Indent columns and conditions in long queries.
• Use descriptive aliases for calculated columns.
• Keep keyword capitalization consistent.
• Write comments for complicated business rules.
• Avoid unnecessary complexity in simple queries.

14. SQL Aliases

An alias provides a temporary name for a column or table within a query. It improves readability without changing the underlying database object name.

Column Alias

SELECT student_name AS name,
       course AS enrolled_course
FROM students;

Alias for a Calculated Column

SELECT COUNT(*) AS total_students
FROM students;

Table Alias

SELECT s.student_name, s.city
FROM students AS s;

Aliases are useful when working with joins, calculated columns, and reports.

15. Introduction to SQL Joins

Joins combine related data from multiple tables based on a specified relationship.

Example Tables

Assume there are two tables: students and courses. The students table contains a course identifier, and the courses table contains course names.

INNER JOIN

SELECT s.student_name, c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.course_id;

INNER JOIN returns rows where the join condition matches in both tables.

LEFT JOIN

SELECT s.student_name, c.course_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.course_id;

LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, right-table columns are returned as NULL.

Common Join Types

16. DCL Commands – Managing Permissions

DCL commands help manage who can access or modify database objects. The precise permissions available depend on the account, roles, and MySQL configuration.

16.1 GRANT

The GRANT statement assigns privileges to a user or role.

GRANT SELECT ON college_db.students
TO 'report_user'@'localhost';

This is an example of granting read access to a table for an existing account. The account must be configured appropriately, and the person issuing the command must have the authority to grant the privilege.

16.2 REVOKE

The REVOKE statement removes previously granted privileges.

REVOKE SELECT ON college_db.students
FROM 'report_user'@'localhost';

Do not run permission-management statements on production systems without understanding their impact. Avoid granting broad privileges when a narrower permission is sufficient.

17. TCL Commands – Transaction Control

Transaction control statements help manage changes to transactional data. Their effects depend on the storage engine and the statements being executed.

17.1 START TRANSACTION

Begins a transaction.

START TRANSACTION;

17.2 COMMIT

Commits the current transaction, making its transactional changes permanent.

COMMIT;

17.3 ROLLBACK

Rolls back uncommitted transactional changes when the relevant operations support rollback.

ROLLBACK;

17.4 SAVEPOINT

Creates a named point within a transaction to which you can roll back.

START TRANSACTION;
SAVEPOINT before_update;
UPDATE students
SET city = 'Pune'
WHERE student_id = 1;
ROLLBACK TO SAVEPOINT before_update;
COMMIT;

This example rolls back the update to the savepoint and then commits the remaining transaction. Transaction control is most useful when working with transactional tables such as InnoDB. Many DDL statements in MySQL cause implicit commits, so they cannot be treated like ordinary rollback-able DML operations.

18. Common SQL Syntax Errors

Error 1: Missing Comma

Incorrect:

SELECT student_name course
FROM students;

This may be interpreted as selecting student_name with an alias rather than two columns. If both columns are required, use a comma.

Correct:

SELECT student_name, course
FROM students;

Error 2: Misspelled Table Name

Incorrect:

SELECT * FROM student;

If the actual table is named students, MySQL reports that the referenced table does not exist.

Correct:

SELECT * FROM students;

Error 3: Incorrect Text Quotation

Incorrect:

SELECT *
FROM students
WHERE city = Mumbai;

Correct:

SELECT *
FROM students
WHERE city = 'Mumbai';

Error 4: Missing WHERE Condition in an UPDATE

Risky statement:

UPDATE students
SET city = 'Mumbai';

This updates the city for every row in the table. If only one student should be changed, specify the intended condition.

UPDATE students
SET city = 'Mumbai'
WHERE student_id = 2;

Error 5: Using NULL Incorrectly

Incorrect:

SELECT *
FROM students
WHERE course = NULL;

Correct:

SELECT *
FROM students
WHERE course IS NULL;

Error 6: Incorrect Clause Order

SQL clauses must appear in the correct order. For example, ORDER BY normally appears after WHERE, GROUP BY, and HAVING in a standard SELECT query.

SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city
ORDER BY total_students DESC;

19. Best Practices for Writing SQL

• Use consistent capitalization and indentation.
• Choose meaningful database, table, and column names.
• Specify the columns needed instead of always using SELECT * in application code.
• Use a WHERE clause when updating or deleting selected records.
• Preview the rows affected by an important update or deletion before executing it.
• Use constraints to enforce data integrity.
• Use parameterized queries in applications instead of building SQL by concatenating untrusted input.
• Use appropriate permissions for each database account.
• Use transactions for related data changes when supported and appropriate.
• Test SQL statements in a development environment before production use.
• Review query results and error messages rather than assuming a statement succeeded.
• Use indexes appropriately for frequently searched or joined columns.
• Back up important data before performing destructive operations.

20. Complete Practical Project – Student Database

In this practical exercise, you will create a database, define a table, insert student records, and execute different SQL statements to understand the syntax.

Step 1: Create and Select the Database

CREATE DATABASE IF NOT EXISTS sql_practice;
USE sql_practice;

Step 2: Create the Table

CREATE TABLE IF NOT EXISTS students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course VARCHAR(100),
    city VARCHAR(100),
    marks DECIMAL(5,2)
);

Step 3: Insert Sample Records

INSERT INTO students (student_name, course, city, marks)
VALUES
('Rahul Sharma', 'MySQL', 'Mumbai', 85.50),
('Priya Patel', 'Python', 'Pune', 92.00),
('Amit Verma', 'MySQL', 'Delhi', 76.00),
('Neha Singh', 'Data Analytics', 'Mumbai', 88.50),
('Karan Mehta', 'Python', 'Pune', 67.00);

Step 4: Retrieve All Records

SELECT * FROM students;

Step 5: Select Specific Columns

SELECT student_name, course
FROM students;

Step 6: Filter Students by City

SELECT *
FROM students
WHERE city = 'Mumbai';

Step 7: Find Students with Marks Above 80

SELECT student_name, marks
FROM students
WHERE marks > 80;

Step 8: Sort Students by Marks

SELECT student_name, marks
FROM students
ORDER BY marks DESC;

Step 9: Count Students by Course

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course;

Step 10: Update a Record

UPDATE students
SET city = 'Navi Mumbai'
WHERE student_id = 1;

Step 11: Verify the Update

SELECT *
FROM students
WHERE student_id = 1;

Step 12: Delete a Specific Record

DELETE FROM students
WHERE student_id = 5;

Only run the deletion after confirming that the selected record is intended to be removed.

Step 13: Verify the Final Records

SELECT * FROM students;
SELECT COUNT(*) AS total_students
FROM students;

Project Learning Outcomes

• Created a database and selected it.
• Defined a table with columns, data types, and a primary key.
• Inserted multiple records.
• Retrieved and filtered data.
• Sorted and grouped query results.
• Updated and deleted a specific record.
• Verified the result of each operation.

21. Interview Questions and Answers

Q1. What is SQL?

SQL stands for Structured Query Language. It is used to define, manipulate, retrieve, and control data in relational database systems.

Q2. What is SQL syntax?

SQL syntax is the set of rules that determines how statements, clauses, identifiers, operators, and values must be arranged in a query.

Q3. What is the difference between DDL and DML?

DDL commands define or modify database structures, while DML commands insert, update, and delete table records.

Q4. What is the purpose of SELECT?

SELECT retrieves data from one or more tables or evaluates expressions.

Q5. What is the purpose of WHERE?

WHERE filters individual rows according to a specified condition.

Q6. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters grouped results, often using aggregate expressions.

Q7. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes selected rows or all rows while retaining the table. TRUNCATE removes all rows while retaining the table structure. DROP removes the table object itself.

Q8. What does ORDER BY do?

ORDER BY sorts the result set in ascending or descending order.

Q9. What is GROUP BY?

GROUP BY groups rows with matching values so aggregate functions can calculate summaries for each group.

Q10. What is a primary key?

A primary key uniquely identifies each row in a table and does not permit NULL values.

Q11. What is a foreign key?

A foreign key defines a relationship between tables by referencing a key in another table or the same table.

Q12. Why is a semicolon used in SQL?

A semicolon commonly marks the end of a SQL statement in interactive clients and scripts. Some application interfaces submit a single statement without requiring a trailing semicolon.

Q13. What is the purpose of an alias?

An alias provides a temporary, readable name for a column, calculated expression, or table within a query.

Q14. What is the purpose of COMMIT?

COMMIT makes the changes in the current transaction permanent for transactional operations.

Q15. What is the purpose of ROLLBACK?

ROLLBACK cancels uncommitted transactional changes when the operations support rollback.

22. Practice Exercises

1. Create a database named company_db.
2. Create an employees table with employee ID, name, department, salary, and city columns.
3. Insert at least five sample employee records.
4. Retrieve all employee records.
5. Display only employee names and departments.
6. Find employees working in Mumbai.
7. Find employees with a salary greater than 40000.
8. Sort employees by salary in descending order.
9. Count employees in each department.
10. Find the highest and lowest salaries.
11. Update the city for one employee using a suitable WHERE condition.
12. Delete one test record after verifying its employee ID.
13. Add an email column to the employees table using ALTER TABLE.
14. Explain the difference between DROP TABLE and TRUNCATE TABLE.
15. Write a SELECT query using WHERE, ORDER BY, and LIMIT together.

23. Quick Revision Cheat Sheet

24. Official MySQL Learning Resources

• MySQL Reference Manual – SQL Statements
• MySQL Reference Manual – SELECT Statement
• MySQL Reference Manual – Data Manipulation Statements
• MySQL Reference Manual – Creating and Selecting a Database

Conclusion

Understanding SQL commands and syntax is a fundamental step in learning MySQL. DDL commands define database structures, DML commands manipulate records, SELECT retrieves data, DCL manages permissions, and transaction control statements manage transactional changes. By practising correct syntax, filtering and sorting data, using constraints, and verifying query results, learners can develop a strong foundation for database development and data analysis.

Continue learning with the JustAcademy MySQL Training Course and register for a MySQL Course Demo to build practical SQL skills.